{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Lab 1: Explore and Benchmark a BigQuery Dataset for Performance\n",
    "\n",
    "## Overview\n",
    "\n",
    "In this lab you will take an existing 2TB+ [TPC-DS benchmark dataset](http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-ds_v2.10.0.pdf) and learn the data warehouse optimization methods you can apply to the dataset in BigQuery to improve performance. \n",
    "\n",
    "### What you'll do\n",
    "\n",
    "In this lab, you will learn how to:\n",
    "\n",
    "- Use BigQuery to access and query the TPC-DS benchmark dataset\n",
    "- Run pre-defined queries to establish baseline performance benchmarks\n",
    "\n",
    "### Prerequisites\n",
    "\n",
    "This is an __advanced level SQL__ lab. Before taking it, you should have experience with SQL. Familiarity with BigQuery is also highly recommended. If you need to get up to speed in these areas, you should take this Data Analyst series of labs first:\n",
    "\n",
    "* [Quest: BigQuery for Data Analysts](https://www.qwiklabs.com/quests/55)\n",
    "\n",
    "Once you're ready, scroll down to learn about the services you will be using and how to properly set up your lab environment.\n",
    "\n",
    "### BigQuery\n",
    "\n",
    "[BigQuery](https://cloud.google.com/bigquery/) is Google's fully managed, NoOps, low cost analytics database. With BigQuery you can query terabytes and terabytes of data without managing infrastructure or needing a database administrator. BigQuery uses SQL and takes advantage of the pay-as-you-go model. BigQuery allows you to focus on analyzing data to find meaningful insights.\n",
    "\n",
    "## TPC-DS Background\n",
    "In order to benchmark the performance of a data warehouse we first must get tables and data to run queries against. There is a public organization, TPC, that provides large benchmarking datasets to companies explicitly for this purpose. The purpose of TPC benchmarks is to provide relevant, objective performance data to industry users.\n",
    "\n",
    "The TPC-DS Dataset we will be using comprises of __25 tables__ and __99 queries__ that simulate common data analysis tasks. View the full documentation [here](http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-ds_v2.11.0.pdf).\n",
    "\n",
    "## Exploring TPC-DS in BigQuery\n",
    "\n",
    "The TPC-DS tables have been loaded into BigQuery and you will explore ways to optimize the performance of common queries by using BigQuery data warehousing best practices. We have limited the size to 2TB for the timing of this lab but the dataset itself can be expanded as needed.\n",
    "\n",
    "Note: The TPC Benchmark and TPC-DS are trademarks of the Transaction Processing Performance Council (http://www.tpc.org). The Cloud DW benchmark is derived from the TPC-DS Benchmark and as such is not comparable to published TPC-DS results."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring the Schema with SQL\n",
    "\n",
    "Question: \n",
    "- How many tables are in the dataset?\n",
    "- What is the name of the largest table (in GB)? How many rows does it have?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dataset_id</th>\n",
       "      <th>table_id</th>\n",
       "      <th>size_gb</th>\n",
       "      <th>creation_time</th>\n",
       "      <th>last_modified_time</th>\n",
       "      <th>row_count</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>store_sales</td>\n",
       "      <td>1545.13</td>\n",
       "      <td>2019-10-13 19:15:03.190000+00:00</td>\n",
       "      <td>2019-10-13 19:15:03.190000+00:00</td>\n",
       "      <td>5762820700</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>catalog_sales</td>\n",
       "      <td>1124.82</td>\n",
       "      <td>2019-10-13 19:14:55.693000+00:00</td>\n",
       "      <td>2019-10-13 19:14:55.693000+00:00</td>\n",
       "      <td>2881495086</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>web_sales</td>\n",
       "      <td>564.63</td>\n",
       "      <td>2019-10-13 19:15:03.986000+00:00</td>\n",
       "      <td>2019-10-13 19:15:03.986000+00:00</td>\n",
       "      <td>1440681379</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>store_returns</td>\n",
       "      <td>129.34</td>\n",
       "      <td>2019-10-13 19:15:01.018000+00:00</td>\n",
       "      <td>2019-10-13 19:15:01.018000+00:00</td>\n",
       "      <td>576280209</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>catalog_returns</td>\n",
       "      <td>81.51</td>\n",
       "      <td>2019-10-13 19:14:54.124000+00:00</td>\n",
       "      <td>2019-10-13 19:14:54.124000+00:00</td>\n",
       "      <td>288154642</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>web_returns</td>\n",
       "      <td>36.43</td>\n",
       "      <td>2019-10-13 19:15:03.043000+00:00</td>\n",
       "      <td>2019-10-13 19:15:03.043000+00:00</td>\n",
       "      <td>144074630</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>customer</td>\n",
       "      <td>1.44</td>\n",
       "      <td>2019-10-13 19:14:54.618000+00:00</td>\n",
       "      <td>2019-10-13 19:14:54.618000+00:00</td>\n",
       "      <td>9100000</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>inventory</td>\n",
       "      <td>1.19</td>\n",
       "      <td>2019-10-13 19:14:57.402000+00:00</td>\n",
       "      <td>2019-10-13 19:14:57.402000+00:00</td>\n",
       "      <td>37584000</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>customer_address</td>\n",
       "      <td>0.61</td>\n",
       "      <td>2019-10-13 19:14:55.631000+00:00</td>\n",
       "      <td>2019-10-13 19:14:55.631000+00:00</td>\n",
       "      <td>4550000</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>customer_demographics</td>\n",
       "      <td>0.13</td>\n",
       "      <td>2019-10-13 19:14:55.340000+00:00</td>\n",
       "      <td>2019-10-13 19:14:55.340000+00:00</td>\n",
       "      <td>1920800</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>item</td>\n",
       "      <td>0.02</td>\n",
       "      <td>2019-10-13 19:14:58.165000+00:00</td>\n",
       "      <td>2019-10-13 19:14:58.165000+00:00</td>\n",
       "      <td>48000</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>date_dim</td>\n",
       "      <td>0.01</td>\n",
       "      <td>2019-10-13 19:14:55.691000+00:00</td>\n",
       "      <td>2019-10-13 19:14:55.691000+00:00</td>\n",
       "      <td>73049</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>time_dim</td>\n",
       "      <td>0.01</td>\n",
       "      <td>2019-10-13 19:15:01.382000+00:00</td>\n",
       "      <td>2019-10-13 19:15:01.382000+00:00</td>\n",
       "      <td>86400</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>call_center</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2019-10-13 19:14:52.907000+00:00</td>\n",
       "      <td>2019-10-13 19:14:52.907000+00:00</td>\n",
       "      <td>8</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>catalog_page</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2019-10-13 19:14:53.022000+00:00</td>\n",
       "      <td>2019-10-13 19:14:53.022000+00:00</td>\n",
       "      <td>11718</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>dbgen_version</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2019-10-13 19:14:56.109000+00:00</td>\n",
       "      <td>2019-10-13 19:14:56.109000+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>household_demographics</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2019-10-13 19:14:56.855000+00:00</td>\n",
       "      <td>2019-10-13 19:14:56.855000+00:00</td>\n",
       "      <td>7200</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>income_band</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2019-10-13 19:14:57.134000+00:00</td>\n",
       "      <td>2019-10-13 19:14:57.134000+00:00</td>\n",
       "      <td>20</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>perf</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2019-10-14 07:04:04.205000+00:00</td>\n",
       "      <td>2019-10-14 08:29:47.141000+00:00</td>\n",
       "      <td>99</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>promotion</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2019-10-13 19:14:58.728000+00:00</td>\n",
       "      <td>2019-10-13 19:14:58.728000+00:00</td>\n",
       "      <td>450</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>reason</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2019-10-13 19:14:59.216000+00:00</td>\n",
       "      <td>2019-10-13 19:14:59.216000+00:00</td>\n",
       "      <td>36</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>ship_mode</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2019-10-13 19:15:00.140000+00:00</td>\n",
       "      <td>2019-10-13 19:15:00.140000+00:00</td>\n",
       "      <td>20</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>store</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2019-10-13 19:15:00.160000+00:00</td>\n",
       "      <td>2019-10-13 19:15:00.160000+00:00</td>\n",
       "      <td>186</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>warehouse</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2019-10-13 19:15:01.593000+00:00</td>\n",
       "      <td>2019-10-13 19:15:01.593000+00:00</td>\n",
       "      <td>6</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>web_page</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2019-10-13 19:15:01.927000+00:00</td>\n",
       "      <td>2019-10-13 19:15:01.927000+00:00</td>\n",
       "      <td>360</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>web_site</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2019-10-13 19:15:03.301000+00:00</td>\n",
       "      <td>2019-10-13 19:15:03.301000+00:00</td>\n",
       "      <td>36</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           dataset_id                table_id  size_gb  \\\n",
       "0   tpcds_2t_baseline             store_sales  1545.13   \n",
       "1   tpcds_2t_baseline           catalog_sales  1124.82   \n",
       "2   tpcds_2t_baseline               web_sales   564.63   \n",
       "3   tpcds_2t_baseline           store_returns   129.34   \n",
       "4   tpcds_2t_baseline         catalog_returns    81.51   \n",
       "5   tpcds_2t_baseline             web_returns    36.43   \n",
       "6   tpcds_2t_baseline                customer     1.44   \n",
       "7   tpcds_2t_baseline               inventory     1.19   \n",
       "8   tpcds_2t_baseline        customer_address     0.61   \n",
       "9   tpcds_2t_baseline   customer_demographics     0.13   \n",
       "10  tpcds_2t_baseline                    item     0.02   \n",
       "11  tpcds_2t_baseline                date_dim     0.01   \n",
       "12  tpcds_2t_baseline                time_dim     0.01   \n",
       "13  tpcds_2t_baseline             call_center     0.00   \n",
       "14  tpcds_2t_baseline            catalog_page     0.00   \n",
       "15  tpcds_2t_baseline           dbgen_version     0.00   \n",
       "16  tpcds_2t_baseline  household_demographics     0.00   \n",
       "17  tpcds_2t_baseline             income_band     0.00   \n",
       "18  tpcds_2t_baseline                    perf     0.00   \n",
       "19  tpcds_2t_baseline               promotion     0.00   \n",
       "20  tpcds_2t_baseline                  reason     0.00   \n",
       "21  tpcds_2t_baseline               ship_mode     0.00   \n",
       "22  tpcds_2t_baseline                   store     0.00   \n",
       "23  tpcds_2t_baseline               warehouse     0.00   \n",
       "24  tpcds_2t_baseline                web_page     0.00   \n",
       "25  tpcds_2t_baseline                web_site     0.00   \n",
       "\n",
       "                      creation_time               last_modified_time  \\\n",
       "0  2019-10-13 19:15:03.190000+00:00 2019-10-13 19:15:03.190000+00:00   \n",
       "1  2019-10-13 19:14:55.693000+00:00 2019-10-13 19:14:55.693000+00:00   \n",
       "2  2019-10-13 19:15:03.986000+00:00 2019-10-13 19:15:03.986000+00:00   \n",
       "3  2019-10-13 19:15:01.018000+00:00 2019-10-13 19:15:01.018000+00:00   \n",
       "4  2019-10-13 19:14:54.124000+00:00 2019-10-13 19:14:54.124000+00:00   \n",
       "5  2019-10-13 19:15:03.043000+00:00 2019-10-13 19:15:03.043000+00:00   \n",
       "6  2019-10-13 19:14:54.618000+00:00 2019-10-13 19:14:54.618000+00:00   \n",
       "7  2019-10-13 19:14:57.402000+00:00 2019-10-13 19:14:57.402000+00:00   \n",
       "8  2019-10-13 19:14:55.631000+00:00 2019-10-13 19:14:55.631000+00:00   \n",
       "9  2019-10-13 19:14:55.340000+00:00 2019-10-13 19:14:55.340000+00:00   \n",
       "10 2019-10-13 19:14:58.165000+00:00 2019-10-13 19:14:58.165000+00:00   \n",
       "11 2019-10-13 19:14:55.691000+00:00 2019-10-13 19:14:55.691000+00:00   \n",
       "12 2019-10-13 19:15:01.382000+00:00 2019-10-13 19:15:01.382000+00:00   \n",
       "13 2019-10-13 19:14:52.907000+00:00 2019-10-13 19:14:52.907000+00:00   \n",
       "14 2019-10-13 19:14:53.022000+00:00 2019-10-13 19:14:53.022000+00:00   \n",
       "15 2019-10-13 19:14:56.109000+00:00 2019-10-13 19:14:56.109000+00:00   \n",
       "16 2019-10-13 19:14:56.855000+00:00 2019-10-13 19:14:56.855000+00:00   \n",
       "17 2019-10-13 19:14:57.134000+00:00 2019-10-13 19:14:57.134000+00:00   \n",
       "18 2019-10-14 07:04:04.205000+00:00 2019-10-14 08:29:47.141000+00:00   \n",
       "19 2019-10-13 19:14:58.728000+00:00 2019-10-13 19:14:58.728000+00:00   \n",
       "20 2019-10-13 19:14:59.216000+00:00 2019-10-13 19:14:59.216000+00:00   \n",
       "21 2019-10-13 19:15:00.140000+00:00 2019-10-13 19:15:00.140000+00:00   \n",
       "22 2019-10-13 19:15:00.160000+00:00 2019-10-13 19:15:00.160000+00:00   \n",
       "23 2019-10-13 19:15:01.593000+00:00 2019-10-13 19:15:01.593000+00:00   \n",
       "24 2019-10-13 19:15:01.927000+00:00 2019-10-13 19:15:01.927000+00:00   \n",
       "25 2019-10-13 19:15:03.301000+00:00 2019-10-13 19:15:03.301000+00:00   \n",
       "\n",
       "     row_count   type  \n",
       "0   5762820700  table  \n",
       "1   2881495086  table  \n",
       "2   1440681379  table  \n",
       "3    576280209  table  \n",
       "4    288154642  table  \n",
       "5    144074630  table  \n",
       "6      9100000  table  \n",
       "7     37584000  table  \n",
       "8      4550000  table  \n",
       "9      1920800  table  \n",
       "10       48000  table  \n",
       "11       73049  table  \n",
       "12       86400  table  \n",
       "13           8  table  \n",
       "14       11718  table  \n",
       "15           1  table  \n",
       "16        7200  table  \n",
       "17          20  table  \n",
       "18          99  table  \n",
       "19         450  table  \n",
       "20          36  table  \n",
       "21          20  table  \n",
       "22         186  table  \n",
       "23           6  table  \n",
       "24         360  table  \n",
       "25          36  table  "
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT \n",
    "  dataset_id,\n",
    "  table_id,\n",
    "  -- Convert bytes to GB.\n",
    "  ROUND(size_bytes/pow(10,9),2) as size_gb,\n",
    "  -- Convert UNIX EPOCH to a timestamp.\n",
    "  TIMESTAMP_MILLIS(creation_time) AS creation_time,\n",
    "  TIMESTAMP_MILLIS(last_modified_time) as last_modified_time,\n",
    "  row_count,\n",
    "  CASE \n",
    "    WHEN type = 1 THEN 'table'\n",
    "    WHEN type = 2 THEN 'view'\n",
    "  ELSE NULL\n",
    "  END AS type\n",
    "FROM\n",
    "  `qwiklabs-resources.tpcds_2t_baseline.__TABLES__`\n",
    "ORDER BY size_gb DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The core tables in the data warehouse are derived from 5 separate core operational systems (each with many tables):\n",
    "\n",
    "![tpc-ds-components.png](img/tpc-ds-components.png)\n",
    "\n",
    "These systems are driven by the core functions of our retail business. As you can see, our store accepts sales from online (web), mail-order (catalog), and in-store. The business must keep track of inventory and can offer promotional discounts on items sold. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exploring all available columns of data\n",
    "\n",
    "Question:\n",
    "- How many columns of data are in the entire dataset (all tables)?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>table_catalog</th>\n",
       "      <th>table_schema</th>\n",
       "      <th>table_name</th>\n",
       "      <th>column_name</th>\n",
       "      <th>ordinal_position</th>\n",
       "      <th>is_nullable</th>\n",
       "      <th>data_type</th>\n",
       "      <th>is_generated</th>\n",
       "      <th>generation_expression</th>\n",
       "      <th>is_stored</th>\n",
       "      <th>is_hidden</th>\n",
       "      <th>is_updatable</th>\n",
       "      <th>is_system_defined</th>\n",
       "      <th>is_partitioning_column</th>\n",
       "      <th>clustering_ordinal_position</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>qwiklabs-resources</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>household_demographics</td>\n",
       "      <td>hd_demo_sk</td>\n",
       "      <td>1</td>\n",
       "      <td>NO</td>\n",
       "      <td>INT64</td>\n",
       "      <td>NEVER</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>qwiklabs-resources</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>household_demographics</td>\n",
       "      <td>hd_income_band_sk</td>\n",
       "      <td>2</td>\n",
       "      <td>YES</td>\n",
       "      <td>INT64</td>\n",
       "      <td>NEVER</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>qwiklabs-resources</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>household_demographics</td>\n",
       "      <td>hd_buy_potential</td>\n",
       "      <td>3</td>\n",
       "      <td>YES</td>\n",
       "      <td>STRING</td>\n",
       "      <td>NEVER</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>qwiklabs-resources</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>household_demographics</td>\n",
       "      <td>hd_dep_count</td>\n",
       "      <td>4</td>\n",
       "      <td>YES</td>\n",
       "      <td>INT64</td>\n",
       "      <td>NEVER</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>qwiklabs-resources</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>household_demographics</td>\n",
       "      <td>hd_vehicle_count</td>\n",
       "      <td>5</td>\n",
       "      <td>YES</td>\n",
       "      <td>INT64</td>\n",
       "      <td>NEVER</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>428</td>\n",
       "      <td>qwiklabs-resources</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>customer</td>\n",
       "      <td>c_birth_year</td>\n",
       "      <td>14</td>\n",
       "      <td>YES</td>\n",
       "      <td>INT64</td>\n",
       "      <td>NEVER</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>429</td>\n",
       "      <td>qwiklabs-resources</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>customer</td>\n",
       "      <td>c_birth_country</td>\n",
       "      <td>15</td>\n",
       "      <td>YES</td>\n",
       "      <td>STRING</td>\n",
       "      <td>NEVER</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>430</td>\n",
       "      <td>qwiklabs-resources</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>customer</td>\n",
       "      <td>c_login</td>\n",
       "      <td>16</td>\n",
       "      <td>YES</td>\n",
       "      <td>STRING</td>\n",
       "      <td>NEVER</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>431</td>\n",
       "      <td>qwiklabs-resources</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>customer</td>\n",
       "      <td>c_email_address</td>\n",
       "      <td>17</td>\n",
       "      <td>YES</td>\n",
       "      <td>STRING</td>\n",
       "      <td>NEVER</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>432</td>\n",
       "      <td>qwiklabs-resources</td>\n",
       "      <td>tpcds_2t_baseline</td>\n",
       "      <td>customer</td>\n",
       "      <td>c_last_review_date_sk</td>\n",
       "      <td>18</td>\n",
       "      <td>YES</td>\n",
       "      <td>INT64</td>\n",
       "      <td>NEVER</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>433 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    table_catalog       table_schema              table_name  \\\n",
       "0     qwiklabs-resources  tpcds_2t_baseline  household_demographics   \n",
       "1     qwiklabs-resources  tpcds_2t_baseline  household_demographics   \n",
       "2     qwiklabs-resources  tpcds_2t_baseline  household_demographics   \n",
       "3     qwiklabs-resources  tpcds_2t_baseline  household_demographics   \n",
       "4     qwiklabs-resources  tpcds_2t_baseline  household_demographics   \n",
       "..            ...                ...                     ...   \n",
       "428   qwiklabs-resources  tpcds_2t_baseline                customer   \n",
       "429   qwiklabs-resources  tpcds_2t_baseline                customer   \n",
       "430   qwiklabs-resources  tpcds_2t_baseline                customer   \n",
       "431   qwiklabs-resources  tpcds_2t_baseline                customer   \n",
       "432   qwiklabs-resources  tpcds_2t_baseline                customer   \n",
       "\n",
       "               column_name  ordinal_position is_nullable data_type  \\\n",
       "0               hd_demo_sk                 1          NO     INT64   \n",
       "1        hd_income_band_sk                 2         YES     INT64   \n",
       "2         hd_buy_potential                 3         YES    STRING   \n",
       "3             hd_dep_count                 4         YES     INT64   \n",
       "4         hd_vehicle_count                 5         YES     INT64   \n",
       "..                     ...               ...         ...       ...   \n",
       "428           c_birth_year                14         YES     INT64   \n",
       "429        c_birth_country                15         YES    STRING   \n",
       "430                c_login                16         YES    STRING   \n",
       "431        c_email_address                17         YES    STRING   \n",
       "432  c_last_review_date_sk                18         YES     INT64   \n",
       "\n",
       "    is_generated generation_expression is_stored is_hidden is_updatable  \\\n",
       "0          NEVER                  None      None        NO         None   \n",
       "1          NEVER                  None      None        NO         None   \n",
       "2          NEVER                  None      None        NO         None   \n",
       "3          NEVER                  None      None        NO         None   \n",
       "4          NEVER                  None      None        NO         None   \n",
       "..           ...                   ...       ...       ...          ...   \n",
       "428        NEVER                  None      None        NO         None   \n",
       "429        NEVER                  None      None        NO         None   \n",
       "430        NEVER                  None      None        NO         None   \n",
       "431        NEVER                  None      None        NO         None   \n",
       "432        NEVER                  None      None        NO         None   \n",
       "\n",
       "    is_system_defined is_partitioning_column clustering_ordinal_position  \n",
       "0                  NO                     NO                        None  \n",
       "1                  NO                     NO                        None  \n",
       "2                  NO                     NO                        None  \n",
       "3                  NO                     NO                        None  \n",
       "4                  NO                     NO                        None  \n",
       "..                ...                    ...                         ...  \n",
       "428                NO                     NO                        None  \n",
       "429                NO                     NO                        None  \n",
       "430                NO                     NO                        None  \n",
       "431                NO                     NO                        None  \n",
       "432                NO                     NO                        None  \n",
       "\n",
       "[433 rows x 15 columns]"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT * FROM \n",
    " `qwiklabs-resources.tpcds_2t_baseline.INFORMATION_SCHEMA.COLUMNS`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Question:\n",
    "- Are any of the columns of data in this baseline dataset partitioned or clustered?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>table_catalog</th>\n",
       "      <th>table_schema</th>\n",
       "      <th>table_name</th>\n",
       "      <th>column_name</th>\n",
       "      <th>ordinal_position</th>\n",
       "      <th>is_nullable</th>\n",
       "      <th>data_type</th>\n",
       "      <th>is_generated</th>\n",
       "      <th>generation_expression</th>\n",
       "      <th>is_stored</th>\n",
       "      <th>is_hidden</th>\n",
       "      <th>is_updatable</th>\n",
       "      <th>is_system_defined</th>\n",
       "      <th>is_partitioning_column</th>\n",
       "      <th>clustering_ordinal_position</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [table_catalog, table_schema, table_name, column_name, ordinal_position, is_nullable, data_type, is_generated, generation_expression, is_stored, is_hidden, is_updatable, is_system_defined, is_partitioning_column, clustering_ordinal_position]\n",
       "Index: []"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT * FROM \n",
    " `qwiklabs-resources.tpcds_2t_baseline.INFORMATION_SCHEMA.COLUMNS`\n",
    "WHERE \n",
    "  is_partitioning_column = 'YES' OR clustering_ordinal_position IS NOT NULL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Question\n",
    "- How many columns of data does each table have (sorted by most to least?)\n",
    "- Which table has the most columns of data?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>column_count</th>\n",
       "      <th>table_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>34</td>\n",
       "      <td>catalog_sales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>34</td>\n",
       "      <td>web_sales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>call_center</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>29</td>\n",
       "      <td>store</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>28</td>\n",
       "      <td>date_dim</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>27</td>\n",
       "      <td>catalog_returns</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>26</td>\n",
       "      <td>web_site</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>24</td>\n",
       "      <td>web_returns</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>23</td>\n",
       "      <td>store_sales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>22</td>\n",
       "      <td>item</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>store_returns</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>19</td>\n",
       "      <td>promotion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>customer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "      <td>warehouse</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>web_page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>13</td>\n",
       "      <td>customer_address</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>10</td>\n",
       "      <td>time_dim</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>9</td>\n",
       "      <td>catalog_page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>9</td>\n",
       "      <td>customer_demographics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>6</td>\n",
       "      <td>ship_mode</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>5</td>\n",
       "      <td>household_demographics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>4</td>\n",
       "      <td>dbgen_version</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>4</td>\n",
       "      <td>inventory</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>4</td>\n",
       "      <td>perf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24</td>\n",
       "      <td>3</td>\n",
       "      <td>income_band</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>3</td>\n",
       "      <td>reason</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    column_count              table_name\n",
       "0             34           catalog_sales\n",
       "1             34               web_sales\n",
       "2             31             call_center\n",
       "3             29                   store\n",
       "4             28                date_dim\n",
       "5             27         catalog_returns\n",
       "6             26                web_site\n",
       "7             24             web_returns\n",
       "8             23             store_sales\n",
       "9             22                    item\n",
       "10            20           store_returns\n",
       "11            19               promotion\n",
       "12            18                customer\n",
       "13            14               warehouse\n",
       "14            14                web_page\n",
       "15            13        customer_address\n",
       "16            10                time_dim\n",
       "17             9            catalog_page\n",
       "18             9   customer_demographics\n",
       "19             6               ship_mode\n",
       "20             5  household_demographics\n",
       "21             4           dbgen_version\n",
       "22             4               inventory\n",
       "23             4                    perf\n",
       "24             3             income_band\n",
       "25             3                  reason"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT \n",
    "  COUNT(column_name) AS column_count, \n",
    "  table_name \n",
    "FROM \n",
    " `qwiklabs-resources.tpcds_2t_baseline.INFORMATION_SCHEMA.COLUMNS`\n",
    "GROUP BY table_name\n",
    "ORDER BY column_count DESC, table_name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Previewing sample rows of data values\n",
    "\n",
    "In the BigQuery UI, find the Resources panel and search for `catalog_sales`. You may need to add the `qwiklabs-resources` project to your UI by clicking __+ Add Data -> Pin a project__ and entering `qwiklabs-resources`\n",
    "\n",
    "Click on the `catalog_sales` table name for the `tpcds_2t_baseline` dataset under `qwiklabs-resources`\n",
    "\n",
    "Question\n",
    "- How many rows are in the table?\n",
    "- How large is the table in TB?\n",
    "\n",
    "Hint: Use the `Details` button in the web UI to quickly access table metadata\n",
    "\n",
    "Question:\n",
    "- `Preview` the data and find the Catalog Sales Extended Sales Price `cs_ext_sales_price` field (which is calculated based on product quantity * sales price)\n",
    "- Are there any missing data values for Catalog Sales Quantity (`cs_quantity`)? \n",
    "- Are there any missing values for cs_ext_ship_cost? For what type of product could this be expected? (Digital products)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create an example sales report\n",
    "\n",
    "Write a query that shows key sales stats for each item sold from the Catalog and execute it in the BigQuery UI:\n",
    "- total orders\n",
    "- total unit quantity\n",
    "- total revenue\n",
    "- total profit\n",
    "- sorted by total orders highest to lowest, limit 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Executing query with job ID: 94a5a441-178c-4efa-a81a-bd450b27bca7\n",
      "Query executing: 69.08s\n",
      "Query complete after 69.67s\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cs_item_sk</th>\n",
       "      <th>total_orders</th>\n",
       "      <th>total_quantity</th>\n",
       "      <th>total_revenue</th>\n",
       "      <th>total_profit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>9253</td>\n",
       "      <td>121222</td>\n",
       "      <td>6103140</td>\n",
       "      <td>307670152.68</td>\n",
       "      <td>-30932953.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1927</td>\n",
       "      <td>121217</td>\n",
       "      <td>6106523</td>\n",
       "      <td>309693527.62</td>\n",
       "      <td>-29261211.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>16603</td>\n",
       "      <td>121181</td>\n",
       "      <td>6099067</td>\n",
       "      <td>307870874.85</td>\n",
       "      <td>-31511409.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>193</td>\n",
       "      <td>121150</td>\n",
       "      <td>6074650</td>\n",
       "      <td>305044085.7</td>\n",
       "      <td>-31913535.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>38845</td>\n",
       "      <td>121142</td>\n",
       "      <td>6096548</td>\n",
       "      <td>306754379.03</td>\n",
       "      <td>-30723801.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>14335</td>\n",
       "      <td>121109</td>\n",
       "      <td>6098853</td>\n",
       "      <td>307479510.28</td>\n",
       "      <td>-30412623.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>21145</td>\n",
       "      <td>121101</td>\n",
       "      <td>6076957</td>\n",
       "      <td>306499554.37</td>\n",
       "      <td>-30122065.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>7255</td>\n",
       "      <td>121098</td>\n",
       "      <td>6075854</td>\n",
       "      <td>308807049.12</td>\n",
       "      <td>-30202631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>37315</td>\n",
       "      <td>121094</td>\n",
       "      <td>6075936</td>\n",
       "      <td>307200736.08</td>\n",
       "      <td>-31155035.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>9973</td>\n",
       "      <td>121091</td>\n",
       "      <td>6089771</td>\n",
       "      <td>308538961.44</td>\n",
       "      <td>-31130175.79</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cs_item_sk  total_orders  total_quantity total_revenue  total_profit\n",
       "0        9253        121222         6103140  307670152.68  -30932953.61\n",
       "1        1927        121217         6106523  309693527.62  -29261211.24\n",
       "2       16603        121181         6099067  307870874.85  -31511409.31\n",
       "3         193        121150         6074650   305044085.7  -31913535.21\n",
       "4       38845        121142         6096548  306754379.03  -30723801.34\n",
       "5       14335        121109         6098853  307479510.28   -30412623.1\n",
       "6       21145        121101         6076957  306499554.37  -30122065.46\n",
       "7        7255        121098         6075854  308807049.12     -30202631\n",
       "8       37315        121094         6075936  307200736.08  -31155035.24\n",
       "9        9973        121091         6089771  308538961.44  -31130175.79"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery --verbose\n",
    "SELECT\n",
    "  cs_item_sk,\n",
    "  COUNT(cs_order_number) AS total_orders,\n",
    "  SUM(cs_quantity) AS total_quantity,\n",
    "  SUM(cs_ext_sales_price) AS total_revenue,\n",
    "  SUM(cs_net_profit) AS total_profit\n",
    "FROM\n",
    "  `qwiklabs-resources.tpcds_2t_baseline.catalog_sales`\n",
    "GROUP BY\n",
    "  cs_item_sk\n",
    "ORDER BY\n",
    "  total_orders DESC\n",
    "LIMIT\n",
    "  100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A note on our data: The TPC-DS benchmark allows data warehouse practicioners to generate any volume of data programmatically. Since the rows of data are system generated, they may not make the most sense in a business context (like why are we selling our top product at such a huge profit loss!).\n",
    "\n",
    "The good news is that to benchmark our performance we care most about the volume of rows and columns to run our benchmark against. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analyzing query performance\n",
    "\n",
    "Click on __Execution details__\n",
    "\n",
    "Refer to the chart below (which should be similar to your results) and answer the following questions.\n",
    "\n",
    "Question\n",
    "- How long did it take the query to run? 5.1s\n",
    "- How much data in GB was processed? 150GB\n",
    "- How much slot time was consumed? 1hr 24min\n",
    "- How many rows were input? 2,881,495,086\n",
    "- How many rows were output as the end result (before the limit)? 23,300\n",
    "- What does the output rows mean in the context of our query? (23,300 unique cs_item_sk)\n",
    "\n",
    "![example-execution-plan](img/example-execution-plan.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Side note: Slot Time\n",
    "\n",
    "We know the query took 5.1 seconds to run so what does the 1hr 24 min slot time metric mean?\n",
    "\n",
    "Inside of the BigQuery service are lots of virtual machines that massively process your data and query logic in parallel. These workers, or \"slots\", work together to process a single query job really quickly. For accounts with on-demand pricing, you can have up to 2,000 slots.\n",
    "\n",
    "So say we had 30 minutes of slot time or 1800 seconds. If the query took 20 seconds in total to run, \n",
    "but it was 1800 seconds worth of work, how many workers at minimum worked on it? \n",
    "1800/20 = 90\n",
    "\n",
    "And that's assuming each worker instantly had all the data it needed (no shuffling of data between workers) and was at full capacity for all 20 seconds!\n",
    "\n",
    "In reality, workers have a variety of tasks (waiting for data, reading it, performing computations, and writing data)\n",
    "and also need to compare notes with eachother on what work was already done on the job. The good news for you is\n",
    "that you don't need to worry about optimizing these workers or the underlying data to run perfectly in parallel. That's why BigQuery is a managed service -- there's an entire team dedicated to hardware and data storage optimization.\n",
    "\n",
    "In case you were wondering, the worker limit for your project is 2,000 slots at once. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Running a performance benchmark\n",
    "\n",
    "To performance benchmark our data warehouse in BigQuery we need to create more than just a single SQL report. The good news is the TPC-DS dataset ships with __99 standard benchmark queries__ that we can run and log the performance outcomes. \n",
    "\n",
    "In this lab, we are doing no adjustments to the existing data warehouse tables (no partitioning, no clustering, no nesting) so we can establish a performance benchmark to beat in future labs."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Viewing the 99 pre-made SQL queries\n",
    "\n",
    "We have a long SQL file with 99 standard queries against this dataset stored in our /sql/ directory.\n",
    "\n",
    "Let's view the first 50 lines of those baseline queries to get familiar with how we will be performance benchmarking our dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "-- start query 1 in stream 0 using template query96.tpl\n",
      "select  count(*) \n",
      "from tpcds_2t_baseline.store_sales\n",
      "    ,tpcds_2t_baseline.household_demographics \n",
      "    ,tpcds_2t_baseline.time_dim, tpcds_2t_baseline.store\n",
      "where ss_sold_time_sk = time_dim.t_time_sk   \n",
      "    and ss_hdemo_sk = household_demographics.hd_demo_sk \n",
      "    and ss_store_sk = s_store_sk\n",
      "    and time_dim.t_hour = 8\n",
      "    and time_dim.t_minute >= 30\n",
      "    and household_demographics.hd_dep_count = 5\n",
      "    and store.s_store_name = 'ese'\n",
      "order by count(*)\n",
      "limit 100;\n",
      "\n",
      "-- end query 1 in stream 0 using template query96.tpl\n",
      "-- start query 2 in stream 0 using template query7.tpl\n",
      "select  i_item_id, \n",
      "        avg(ss_quantity) agg1,\n",
      "        avg(ss_list_price) agg2,\n",
      "        avg(ss_coupon_amt) agg3,\n",
      "        avg(ss_sales_price) agg4 \n",
      " from tpcds_2t_baseline.store_sales, tpcds_2t_baseline.customer_demographics, tpcds_2t_baseline.date_dim, tpcds_2t_baseline.item, tpcds_2t_baseline.promotion\n",
      " where ss_sold_date_sk = d_date_sk and\n",
      "       ss_item_sk = i_item_sk and\n",
      "       ss_cdemo_sk = cd_demo_sk and\n",
      "       ss_promo_sk = p_promo_sk and\n",
      "       cd_gender = 'M' and \n",
      "       cd_marital_status = 'M' and\n",
      "       cd_education_status = '4 yr Degree' and\n",
      "       (p_channel_email = 'N' or p_channel_event = 'N') and\n",
      "       d_year = 2001 \n",
      " group by i_item_id\n",
      " order by i_item_id\n",
      " limit 100;\n",
      "\n",
      "-- end query 2 in stream 0 using template query7.tpl\n",
      "-- start query 3 in stream 0 using template query75.tpl\n",
      "WITH all_sales AS (\n",
      " SELECT d_year\n",
      "       ,i_brand_id\n",
      "       ,i_class_id\n",
      "       ,i_category_id\n",
      "       ,i_manufact_id\n",
      "       ,SUM(sales_cnt) AS sales_cnt\n",
      "       ,SUM(sales_amt) AS sales_amt\n",
      " FROM (SELECT d_year\n",
      "             ,i_brand_id\n",
      "             ,i_class_id\n"
     ]
    }
   ],
   "source": [
    "!head --lines=50 'sql/example_baseline_queries.sql'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Running the first benchmark test\n",
    "Now let's run the first query against our dataset and note the execution time. Tip: You can use the [--verbose flag](https://googleapis.dev/python/bigquery/latest/magics.html) in %%bigquery magics to return the job and completion time. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Executing query with job ID: 0a6ca437-346e-4416-aa3a-2d279a3631ae\n",
      "Query executing: 1.08s\n",
      "Query complete after 1.90s\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>f0_</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1488312</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       f0_\n",
       "0  1488312"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery --verbose\n",
    "# start query 1 in stream 0 using template query96.tpl\n",
    "select  count(*) \n",
    "from `qwiklabs-resources.tpcds_2t_baseline.store_sales` as store_sales\n",
    "    ,`qwiklabs-resources.tpcds_2t_baseline.household_demographics` as household_demographics \n",
    "    ,`qwiklabs-resources.tpcds_2t_baseline.time_dim` as time_dim, \n",
    "    `qwiklabs-resources.tpcds_2t_baseline.store` as store\n",
    "where ss_sold_time_sk = time_dim.t_time_sk   \n",
    "    and ss_hdemo_sk = household_demographics.hd_demo_sk \n",
    "    and ss_store_sk = s_store_sk\n",
    "    and time_dim.t_hour = 8\n",
    "    and time_dim.t_minute >= 30\n",
    "    and household_demographics.hd_dep_count = 5\n",
    "    and store.s_store_name = 'ese'\n",
    "order by count(*)\n",
    "limit 100;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It should execute in just a few seconds. Then try running it again and see if you get the same performance. BigQuery will automatically [cache the results](https://cloud.google.com/bigquery/docs/cached-results) from the first time you ran the query and then serve those same results to you when you can the query again. We can confirm this by analyzing the query job statistics. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Viewing BigQuery job statistics\n",
    "\n",
    "Let's list our five most recent query jobs run on BigQuery using the `bq` [command line interface](https://cloud.google.com/bigquery/docs/managing-jobs#viewing_information_about_jobs). Then we will get even more detail on our most recent job with the `bq show` command. Be sure to replace the job id with your own."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                 jobId                   Job Type    State      Start Time         Duration     \n",
      " -------------------------------------- ---------- --------- ----------------- ---------------- \n",
      "  14ec0f66-91ab-41f7-bf1f-3e5660cb9d3f   query      SUCCESS   15 Oct 15:34:27   0:00:03.835000  \n",
      "  <REDACTED>                             None       FAILURE   15 Oct 15:33:01   0:00:17.561000  \n",
      "  94a5a441-178c-4efa-a81a-bd450b27bca7   query      SUCCESS   15 Oct 15:32:15   0:01:09.065000  \n",
      "  7fab5973-be13-4b84-acd5-63c2c3946225   query      SUCCESS   15 Oct 15:31:40   0:00:00.671000  \n",
      "  5224adc7-d6d1-463a-9820-bb71139e27aa   query      SUCCESS   15 Oct 15:31:32   0:00:00.572000  \n"
     ]
    }
   ],
   "source": [
    "!bq ls -j -a -n 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\n",
      "  \"configuration\": {\n",
      "    \"dryRun\": false, \n",
      "    \"jobType\": \"QUERY\", \n",
      "    \"query\": {\n",
      "      \"createDisposition\": \"CREATE_IF_NEEDED\", \n",
      "      \"destinationTable\": {\n",
      "        \"datasetId\": \"_26faa896567219ad1a8a420ad92caebbb6af636a\", \n",
      "        \"projectId\": \"qwiklabs-resources\", \n",
      "        \"tableId\": \"anon0e42104bd9f24ce9a8762cb60cc3f67fa6aadf66\"\n",
      "      }, \n",
      "      \"priority\": \"INTERACTIVE\", \n",
      "      \"query\": \"# start query 1 in stream 0 using template query96.tpl\\nselect  count(*) \\nfrom tpcds_2t_baseline.store_sales\\n    ,tpcds_2t_baseline.household_demographics \\n    ,tpcds_2t_baseline.time_dim, tpcds_2t_baseline.store\\nwhere ss_sold_time_sk = time_dim.t_time_sk   \\n    and ss_hdemo_sk = household_demographics.hd_demo_sk \\n    and ss_store_sk = s_store_sk\\n    and time_dim.t_hour = 8\\n    and time_dim.t_minute >= 30\\n    and household_demographics.hd_dep_count = 5\\n    and store.s_store_name = 'ese'\\norder by count(*)\\nlimit 100;\\n\", \n",
      "      \"useLegacySql\": false, \n",
      "      \"writeDisposition\": \"WRITE_TRUNCATE\"\n",
      "    }\n",
      "  }, \n",
      "  \"etag\": \"8VQdnl1bV4S/9jpByl9Qvg==\", \n",
      "  \"id\": \"qwiklabs-resources:US.612a4b28-cb5c-4e0b-ad5b-ebd51c3b2439\", \n",
      "  \"jobReference\": {\n",
      "    \"jobId\": \"612a4b28-cb5c-4e0b-ad5b-ebd51c3b2439\", \n",
      "    \"location\": \"US\", \n",
      "    \"projectId\": \"qwiklabs-resources\"\n",
      "  }, \n",
      "  \"kind\": \"bigquery#job\", \n",
      "  \"selfLink\": \"https://bigquery.googleapis.com/bigquery/v2/projects/qwiklabs-resources/jobs/612a4b28-cb5c-4e0b-ad5b-ebd51c3b2439?location=US\", \n",
      "  \"statistics\": {\n",
      "    \"creationTime\": \"1571002972799\", \n",
      "    \"endTime\": \"1571002973547\", \n",
      "    \"query\": {\n",
      "      \"cacheHit\": true, \n",
      "      \"statementType\": \"SELECT\", \n",
      "      \"totalBytesBilled\": \"0\", \n",
      "      \"totalBytesProcessed\": \"0\"\n",
      "    }, \n",
      "    \"startTime\": \"1571002973360\", \n",
      "    \"totalBytesProcessed\": \"0\"\n",
      "  }, \n",
      "  \"status\": {\n",
      "    \"state\": \"DONE\"\n",
      "  }, \n",
      "  \"user_email\": \"796822948179-compute@developer.gserviceaccount.com\"\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "!bq show --format=prettyjson -j 612a4b28-cb5c-4e0b-ad5b-ebd51c3b2439"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looking at the job statistics we can see our most recent query hit cache \n",
    "- `cacheHit: true` and therefore \n",
    "- `totalBytesProcessed: 0`. \n",
    "\n",
    "While this is great in normal uses for BigQuery (you aren't charged for queries that hit cache) it kind of ruins our performance test. While cache is super useful we want to disable it for testing purposes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Disabling Cache and Dry Running Queries\n",
    "As of the time this lab was created, you can't pass a flag to `%%bigquery` iPython notebook magics to disable cache or to quickly see the amount of data processed. So we will use the traditional `bq` [command line interface in bash](https://cloud.google.com/bigquery/docs/reference/bq-cli-reference#bq_query).\n",
    "\n",
    "First we will do a `dry run` of the query without processing any data just to see how many bytes of data would be processed. Then we will remove that flag and ensure `nouse_cache` is set to avoid hitting cache as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash \n",
    "bq query \\\n",
    "--dry_run \\\n",
    "--nouse_cache \\\n",
    "--use_legacy_sql=false \\\n",
    "\"\"\"\\\n",
    "select  count(*) \n",
    "from \\`qwiklabs-resources.tpcds_2t_baseline.store_sales\\` as store_sales\n",
    "    ,\\`qwiklabs-resources.tpcds_2t_baseline.household_demographics\\` as household_demographics  \n",
    "    ,\\`qwiklabs-resources.tpcds_2t_baseline.time_dim\\` as time_dim, \\`qwiklabs-resources.tpcds_2t_baseline.store\\` as store\n",
    "where ss_sold_time_sk = time_dim.t_time_sk   \n",
    "    and ss_hdemo_sk = household_demographics.hd_demo_sk \n",
    "    and ss_store_sk = s_store_sk\n",
    "    and time_dim.t_hour = 8\n",
    "    and time_dim.t_minute >= 30\n",
    "    and household_demographics.hd_dep_count = 5\n",
    "    and store.s_store_name = 'ese'\n",
    "order by count(*)\n",
    "limit 100;\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "132.086388641"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert bytes to GB\n",
    "132086388641 / 1e+9"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "132 GB will be processed. At the time of writing, [BigQuery pricing](https://cloud.google.com/bigquery/pricing) is \\\\$5 per 1 TB (or 1000 GB) of data after the first free 1 TB each month. Assuming we've exhausted our 1 TB free this month, this would be \\\\$0.66 to run.\n",
    "\n",
    "Now let's run it an ensure we're not pulling from cache so we get an accurate time-to-completion benchmark."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash \n",
    "bq query \\\n",
    "--nouse_cache \\\n",
    "--use_legacy_sql=false \\\n",
    "\"\"\"\\\n",
    "select  count(*) \n",
    "from \\`qwiklabs-resources.tpcds_2t_baseline.store_sales\\` as store_sales\n",
    "    ,\\`qwiklabs-resources.tpcds_2t_baseline.household_demographics\\` as household_demographics  \n",
    "    ,\\`qwiklabs-resources.tpcds_2t_baseline.time_dim\\` as time_dim, \\`qwiklabs-resources.tpcds_2t_baseline.store\\` as store\n",
    "where ss_sold_time_sk = time_dim.t_time_sk   \n",
    "    and ss_hdemo_sk = household_demographics.hd_demo_sk \n",
    "    and ss_store_sk = s_store_sk\n",
    "    and time_dim.t_hour = 8\n",
    "    and time_dim.t_minute >= 30\n",
    "    and household_demographics.hd_dep_count = 5\n",
    "    and store.s_store_name = 'ese'\n",
    "order by count(*)\n",
    "limit 100;\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you're an experienced BigQuery user, you likely have seen these same metrics in the Web UI as well as highlighted in the red box below:\n",
    "\n",
    "![img/bq-ui-results.png](img/bq-ui-results.png)\n",
    "\n",
    "It's a matter of preference whether you do your work in the Web UI or the command line -- each has it's advantages.\n",
    "\n",
    "One major advantage of using the `bq` command line interface is the ability to create a script that will run the remaining 98 benchmark queries for us and log the results. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Copy the qwiklabs-resources dataset into your own GCP project\n",
    "\n",
    "We will use the new [BigQuery Transfer Service](https://cloud.google.com/bigquery/docs/copying-datasets) to quickly copy our large dataset from the `qwiklabs-resources` GCP project into your own so you can perform the benchmarking. \n",
    "\n",
    "### Create a new baseline dataset in your project"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "\n",
    "export PROJECT_ID=$(gcloud config list --format 'value(core.project)')\n",
    "export BENCHMARK_DATASET_NAME=tpcds_2t_baseline # Name of the dataset you want to create\n",
    "\n",
    "## Create a BigQuery dataset for tpcds_2t_flat_part_clust if it doesn't exist\n",
    "datasetexists=$(bq ls -d | grep -w $BENCHMARK_DATASET_NAME)\n",
    "\n",
    "if [ -n \"$datasetexists\" ]; then\n",
    "    echo -e \"BigQuery dataset $BENCHMARK_DATASET_NAME already exists, let's not recreate it.\"\n",
    "\n",
    "else\n",
    "    echo \"Creating BigQuery dataset titled: $BENCHMARK_DATASET_NAME\"\n",
    "    \n",
    "    bq --location=US mk --dataset \\\n",
    "        --description 'Benchmark Dataset' \\\n",
    "        $PROJECT:$BENCHMARK_DATASET_NAME\n",
    "   echo \"\\nHere are your current datasets:\"\n",
    "   bq ls\n",
    "fi"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Use the BigQuery Data Transfer Service to copy an existing dataset\n",
    "\n",
    "1. Enable the [BigQuery Data Transfer Service API](https://console.cloud.google.com/apis/library/bigquerydatatransfer.googleapis.com)\n",
    "2. Navigate to the [BigQuery console and the existing `qwiklabs-resources` dataset](https://console.cloud.google.com/bigquery?project=qwiklabs-resources&p=qwiklabs-resources&d=tpcds_2t_baseline&page=dataset)\n",
    "\n",
    "![img/open-dataset.png](img/open-dataset.png)\n",
    "\n",
    "3. Click Copy Dataset\n",
    "\n",
    "![img/copy-dataset-2.png](img/copy-dataset-2.png)\n",
    "\n",
    "4. In the pop-up, choose your __project name__ and the newly created __dataset name__ from the previous step\n",
    "\n",
    "![img/copy-dataset-modal.png](img/copy-dataset-modal.png)\n",
    "\n",
    "5. Click __Copy__\n",
    "\n",
    "6. Wait for the transfer to complete\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Verify you now have the baseline data in your project\n",
    "\n",
    "Run the below query and confirm you see data. Note that if you omit the `project-id` ahead of the dataset name in the `FROM` clause, BigQuery will assume your default project."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>store_transaction_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>5762820700</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   store_transaction_count\n",
       "0               5762820700"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT COUNT(*) AS store_transaction_count\n",
    "FROM tpcds_2t_baseline.store_sales"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Setup an automated test\n",
    "\n",
    "Running each of the 99 queries manually via the Console UI would be a tedious effort. We'll show you how you can run all 99 programmatically and automatically log the output (time and GB processed) to a log file for analysis. \n",
    "\n",
    "Below is a shell script that:\n",
    "1. Accepts a BigQuery dataset to benchmark\n",
    "2. Accepts a list of semi-colon separated queries to run\n",
    "3. Loops through each query and calls the `bq` query command\n",
    "4. Records the execution time into a separate BigQuery performance table `perf`\n",
    "\n",
    "Execute the below statement and follow along with the results as you benchmark a few example queries (don't worry, we've already ran the full 99 recently so you won't have to).\n",
    "\n",
    "__After executing, wait 1-2 minutes for the benchmark test to complete__\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Process is interrupted.\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "# runs the SQL queries from the TPCDS benchmark \n",
    "\n",
    "# Pull the current Google Cloud Platform project name\n",
    "\n",
    "BQ_DATASET=\"tpcds_2t_baseline\" # let's start by benchmarking our baseline dataset \n",
    "QUERY_FILE_PATH=\"./sql/example_baseline_queries.sql\" # the full test is on 99_baseline_queries but that will take 80+ mins to run\n",
    "IFS=\";\"\n",
    "\n",
    "# create perf table to keep track of run times for all 99 queries\n",
    "printf \"\\033[32;1m Housekeeping tasks... \\033[0m\\n\\n\";\n",
    "printf \"Creating a reporting table perf to track how fast each query runs...\";\n",
    "perf_table_ddl=\"CREATE TABLE IF NOT EXISTS $BQ_DATASET.perf(performance_test_num int64, query_num int64, elapsed_time_sec int64, ran_on int64)\"\n",
    "bq rm -f $BQ_DATASET.perf\n",
    "bq query --nouse_legacy_sql $perf_table_ddl \n",
    "\n",
    "start=$(date +%s)\n",
    "index=0\n",
    "for select_stmt in $(<$QUERY_FILE_PATH)　\n",
    "do \n",
    "  # run the test until you hit a line with the string 'END OF BENCHMARK' in the file\n",
    "  if [[ \"$select_stmt\" == *'END OF BENCHMARK'* ]]; then\n",
    "    break\n",
    "  fi\n",
    "\n",
    "  printf \"\\n\\033[32;1m Let's benchmark this query... \\033[0m\\n\";\n",
    "  printf \"$select_stmt\";\n",
    "  \n",
    "  SECONDS=0;\n",
    "  bq query --use_cache=false --nouse_legacy_sql $select_stmt # critical to turn cache off for this test\n",
    "  duration=$SECONDS\n",
    "\n",
    "  # get current timestamp in milliseconds  \n",
    "  ran_on=$(date +%s)\n",
    "\n",
    "  index=$((index+1))\n",
    "\n",
    "  printf \"\\n\\033[32;1m Here's how long it took... \\033[0m\\n\\n\";\n",
    "  echo \"Query $index ran in $(($duration / 60)) minutes and $(($duration % 60)) seconds.\"\n",
    "\n",
    "  printf \"\\n\\033[32;1m Writing to our benchmark table... \\033[0m\\n\\n\";\n",
    "  insert_stmt=\"insert into $BQ_DATASET.perf(performance_test_num, query_num, elapsed_time_sec, ran_on) values($start, $index, $duration, $ran_on)\"\n",
    "  printf \"$insert_stmt\"\n",
    "  bq query --nouse_legacy_sql $insert_stmt\n",
    "done\n",
    "\n",
    "end=$(date +%s)\n",
    "\n",
    "printf \"Benchmark test complete\"\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Viewing the benchmark results\n",
    "\n",
    "As part of the benchmark test, we stored the processing time of each query into a new `perf` BigQuery table. We can query that table and get some performance stats for our test. \n",
    "\n",
    "First are each of the tests we ran:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>performance_test_num</th>\n",
       "      <th>query_num</th>\n",
       "      <th>elapsed_time_sec</th>\n",
       "      <th>ran_on</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1571014412</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1571014417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1571014412</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>1571014430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1571014412</td>\n",
       "      <td>3</td>\n",
       "      <td>25</td>\n",
       "      <td>1571014458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1571014412</td>\n",
       "      <td>4</td>\n",
       "      <td>25</td>\n",
       "      <td>1571014487</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   performance_test_num  query_num  elapsed_time_sec      ran_on\n",
       "0            1571014412          1                 5  1571014417\n",
       "1            1571014412          2                10  1571014430\n",
       "2            1571014412          3                25  1571014458\n",
       "3            1571014412          4                25  1571014487"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT * FROM tpcds_2t_baseline.perf\n",
    "WHERE \n",
    " # Let's only pull the results from our most recent test\n",
    " performance_test_num = (SELECT MAX(performance_test_num) FROM tpcds_2t_baseline.perf)\n",
    "ORDER BY ran_on"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And finally, the overall statistics for the entire test:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>test_date</th>\n",
       "      <th>latest_performance_test_num</th>\n",
       "      <th>count_queries_benchmarked</th>\n",
       "      <th>total_time_sec</th>\n",
       "      <th>fastest_query_time_sec</th>\n",
       "      <th>slowest_query_time_sec</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2019-10-14 00:53:32+00:00</td>\n",
       "      <td>1571014412</td>\n",
       "      <td>4</td>\n",
       "      <td>65</td>\n",
       "      <td>5</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  test_date  latest_performance_test_num  \\\n",
       "0 2019-10-14 00:53:32+00:00                   1571014412   \n",
       "\n",
       "   count_queries_benchmarked  total_time_sec  fastest_query_time_sec  \\\n",
       "0                          4              65                       5   \n",
       "\n",
       "   slowest_query_time_sec  \n",
       "0                      25  "
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT\n",
    "  TIMESTAMP_SECONDS(MAX(performance_test_num)) AS test_date,\n",
    "  MAX(performance_test_num) AS latest_performance_test_num,\n",
    "  COUNT(DISTINCT query_num) AS count_queries_benchmarked,\n",
    "  SUM(elapsed_time_sec) AS total_time_sec,\n",
    "  MIN(elapsed_time_sec) AS fastest_query_time_sec,\n",
    "  MAX(elapsed_time_sec) AS slowest_query_time_sec\n",
    "FROM\n",
    "  tpcds_2t_baseline.perf\n",
    "WHERE\n",
    "  performance_test_num = (SELECT MAX(performance_test_num) FROM tpcds_2t_baseline.perf)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Benchmarking all 99 queries\n",
    "\n",
    "As we mentioned before, we already ran all 99 queries and recorded the results and made them available for you to query in a public table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>test_date</th>\n",
       "      <th>query_num</th>\n",
       "      <th>query_ran_on</th>\n",
       "      <th>query_completed_on</th>\n",
       "      <th>elapsed_time_sec</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-10-15 15:36:28+00:00</td>\n",
       "      <td>2019-10-15 15:36:32+00:00</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>2</td>\n",
       "      <td>2019-10-15 15:37:22+00:00</td>\n",
       "      <td>2019-10-15 15:38:13+00:00</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>3</td>\n",
       "      <td>2019-10-15 15:38:16+00:00</td>\n",
       "      <td>2019-10-15 15:39:06+00:00</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-10-15 15:39:10+00:00</td>\n",
       "      <td>2019-10-15 15:40:00+00:00</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>5</td>\n",
       "      <td>2019-10-15 15:39:23+00:00</td>\n",
       "      <td>2019-10-15 15:39:32+00:00</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>6</td>\n",
       "      <td>2019-10-15 15:40:53+00:00</td>\n",
       "      <td>2019-10-15 15:42:20+00:00</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>7</td>\n",
       "      <td>2019-10-15 15:41:33+00:00</td>\n",
       "      <td>2019-10-15 15:42:09+00:00</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>8</td>\n",
       "      <td>2019-10-15 15:41:50+00:00</td>\n",
       "      <td>2019-10-15 15:42:03+00:00</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>9</td>\n",
       "      <td>2019-10-15 15:43:44+00:00</td>\n",
       "      <td>2019-10-15 15:45:35+00:00</td>\n",
       "      <td>111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>10</td>\n",
       "      <td>2019-10-15 15:47:33+00:00</td>\n",
       "      <td>2019-10-15 15:51:19+00:00</td>\n",
       "      <td>226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>11</td>\n",
       "      <td>2019-10-15 15:48:02+00:00</td>\n",
       "      <td>2019-10-15 15:48:27+00:00</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>12</td>\n",
       "      <td>2019-10-15 15:49:13+00:00</td>\n",
       "      <td>2019-10-15 15:50:21+00:00</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>13</td>\n",
       "      <td>2019-10-15 15:49:33+00:00</td>\n",
       "      <td>2019-10-15 15:49:50+00:00</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>14</td>\n",
       "      <td>2019-10-15 15:49:41+00:00</td>\n",
       "      <td>2019-10-15 15:49:46+00:00</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>15</td>\n",
       "      <td>2019-10-15 15:50:21+00:00</td>\n",
       "      <td>2019-10-15 15:50:57+00:00</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>16</td>\n",
       "      <td>2019-10-15 15:50:49+00:00</td>\n",
       "      <td>2019-10-15 15:51:14+00:00</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>17</td>\n",
       "      <td>2019-10-15 15:51:57+00:00</td>\n",
       "      <td>2019-10-15 15:52:48+00:00</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>18</td>\n",
       "      <td>2019-10-15 15:54:46+00:00</td>\n",
       "      <td>2019-10-15 15:57:32+00:00</td>\n",
       "      <td>166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>19</td>\n",
       "      <td>2019-10-15 15:55:15+00:00</td>\n",
       "      <td>2019-10-15 15:55:41+00:00</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>20</td>\n",
       "      <td>2019-10-15 15:59:36+00:00</td>\n",
       "      <td>2019-10-15 16:03:54+00:00</td>\n",
       "      <td>258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>21</td>\n",
       "      <td>2019-10-15 16:00:04+00:00</td>\n",
       "      <td>2019-10-15 16:00:29+00:00</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>22</td>\n",
       "      <td>2019-10-15 16:00:59+00:00</td>\n",
       "      <td>2019-10-15 16:01:50+00:00</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>23</td>\n",
       "      <td>2019-10-15 16:02:10+00:00</td>\n",
       "      <td>2019-10-15 16:03:18+00:00</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>24</td>\n",
       "      <td>2019-10-15 16:02:31+00:00</td>\n",
       "      <td>2019-10-15 16:02:48+00:00</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>25</td>\n",
       "      <td>2019-10-15 16:05:55+00:00</td>\n",
       "      <td>2019-10-15 16:09:12+00:00</td>\n",
       "      <td>197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>26</td>\n",
       "      <td>2019-10-15 16:07:06+00:00</td>\n",
       "      <td>2019-10-15 16:08:14+00:00</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>26</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>27</td>\n",
       "      <td>2019-10-15 16:07:17+00:00</td>\n",
       "      <td>2019-10-15 16:07:25+00:00</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>28</td>\n",
       "      <td>2019-10-15 16:08:12+00:00</td>\n",
       "      <td>2019-10-15 16:09:03+00:00</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>29</td>\n",
       "      <td>2019-10-15 16:09:06+00:00</td>\n",
       "      <td>2019-10-15 16:09:57+00:00</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>29</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>30</td>\n",
       "      <td>2019-10-15 16:10:00+00:00</td>\n",
       "      <td>2019-10-15 16:10:51+00:00</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>30</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>31</td>\n",
       "      <td>2019-10-15 16:10:56+00:00</td>\n",
       "      <td>2019-10-15 16:11:46+00:00</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>32</td>\n",
       "      <td>2019-10-15 16:11:43+00:00</td>\n",
       "      <td>2019-10-15 16:12:20+00:00</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>32</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>33</td>\n",
       "      <td>2019-10-15 16:12:54+00:00</td>\n",
       "      <td>2019-10-15 16:14:02+00:00</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>33</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>34</td>\n",
       "      <td>2019-10-15 16:14:05+00:00</td>\n",
       "      <td>2019-10-15 16:15:13+00:00</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>34</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>35</td>\n",
       "      <td>2019-10-15 16:16:27+00:00</td>\n",
       "      <td>2019-10-15 16:18:44+00:00</td>\n",
       "      <td>137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>35</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>36</td>\n",
       "      <td>2019-10-15 16:17:12+00:00</td>\n",
       "      <td>2019-10-15 16:17:51+00:00</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>36</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>37</td>\n",
       "      <td>2019-10-15 16:18:05+00:00</td>\n",
       "      <td>2019-10-15 16:18:55+00:00</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>37</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>38</td>\n",
       "      <td>2019-10-15 16:20:55+00:00</td>\n",
       "      <td>2019-10-15 16:23:41+00:00</td>\n",
       "      <td>166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>38</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>39</td>\n",
       "      <td>2019-10-15 16:21:26+00:00</td>\n",
       "      <td>2019-10-15 16:21:52+00:00</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>39</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>40</td>\n",
       "      <td>2019-10-15 16:21:48+00:00</td>\n",
       "      <td>2019-10-15 16:22:06+00:00</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>40</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>41</td>\n",
       "      <td>2019-10-15 16:25:39+00:00</td>\n",
       "      <td>2019-10-15 16:29:26+00:00</td>\n",
       "      <td>227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>41</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>42</td>\n",
       "      <td>2019-10-15 16:27:33+00:00</td>\n",
       "      <td>2019-10-15 16:29:24+00:00</td>\n",
       "      <td>111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>42</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>43</td>\n",
       "      <td>2019-10-15 16:31:37+00:00</td>\n",
       "      <td>2019-10-15 16:35:24+00:00</td>\n",
       "      <td>227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>43</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>44</td>\n",
       "      <td>2019-10-15 16:32:08+00:00</td>\n",
       "      <td>2019-10-15 16:32:33+00:00</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>44</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>45</td>\n",
       "      <td>2019-10-15 16:32:22+00:00</td>\n",
       "      <td>2019-10-15 16:32:33+00:00</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>45</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>46</td>\n",
       "      <td>2019-10-15 16:34:42+00:00</td>\n",
       "      <td>2019-10-15 16:36:59+00:00</td>\n",
       "      <td>137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>46</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>47</td>\n",
       "      <td>2019-10-15 16:36:14+00:00</td>\n",
       "      <td>2019-10-15 16:37:43+00:00</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>47</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>48</td>\n",
       "      <td>2019-10-15 16:37:26+00:00</td>\n",
       "      <td>2019-10-15 16:38:34+00:00</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>48</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>49</td>\n",
       "      <td>2019-10-15 16:38:06+00:00</td>\n",
       "      <td>2019-10-15 16:38:43+00:00</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>49</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>50</td>\n",
       "      <td>2019-10-15 16:39:01+00:00</td>\n",
       "      <td>2019-10-15 16:39:53+00:00</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>51</td>\n",
       "      <td>2019-10-15 16:43:30+00:00</td>\n",
       "      <td>2019-10-15 16:47:47+00:00</td>\n",
       "      <td>257</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   test_date  query_num              query_ran_on  \\\n",
       "0  2019-10-15 15:36:24+00:00          1 2019-10-15 15:36:28+00:00   \n",
       "1  2019-10-15 15:36:24+00:00          2 2019-10-15 15:37:22+00:00   \n",
       "2  2019-10-15 15:36:24+00:00          3 2019-10-15 15:38:16+00:00   \n",
       "3  2019-10-15 15:36:24+00:00          4 2019-10-15 15:39:10+00:00   \n",
       "4  2019-10-15 15:36:24+00:00          5 2019-10-15 15:39:23+00:00   \n",
       "5  2019-10-15 15:36:24+00:00          6 2019-10-15 15:40:53+00:00   \n",
       "6  2019-10-15 15:36:24+00:00          7 2019-10-15 15:41:33+00:00   \n",
       "7  2019-10-15 15:36:24+00:00          8 2019-10-15 15:41:50+00:00   \n",
       "8  2019-10-15 15:36:24+00:00          9 2019-10-15 15:43:44+00:00   \n",
       "9  2019-10-15 15:36:24+00:00         10 2019-10-15 15:47:33+00:00   \n",
       "10 2019-10-15 15:36:24+00:00         11 2019-10-15 15:48:02+00:00   \n",
       "11 2019-10-15 15:36:24+00:00         12 2019-10-15 15:49:13+00:00   \n",
       "12 2019-10-15 15:36:24+00:00         13 2019-10-15 15:49:33+00:00   \n",
       "13 2019-10-15 15:36:24+00:00         14 2019-10-15 15:49:41+00:00   \n",
       "14 2019-10-15 15:36:24+00:00         15 2019-10-15 15:50:21+00:00   \n",
       "15 2019-10-15 15:36:24+00:00         16 2019-10-15 15:50:49+00:00   \n",
       "16 2019-10-15 15:36:24+00:00         17 2019-10-15 15:51:57+00:00   \n",
       "17 2019-10-15 15:36:24+00:00         18 2019-10-15 15:54:46+00:00   \n",
       "18 2019-10-15 15:36:24+00:00         19 2019-10-15 15:55:15+00:00   \n",
       "19 2019-10-15 15:36:24+00:00         20 2019-10-15 15:59:36+00:00   \n",
       "20 2019-10-15 15:36:24+00:00         21 2019-10-15 16:00:04+00:00   \n",
       "21 2019-10-15 15:36:24+00:00         22 2019-10-15 16:00:59+00:00   \n",
       "22 2019-10-15 15:36:24+00:00         23 2019-10-15 16:02:10+00:00   \n",
       "23 2019-10-15 15:36:24+00:00         24 2019-10-15 16:02:31+00:00   \n",
       "24 2019-10-15 15:36:24+00:00         25 2019-10-15 16:05:55+00:00   \n",
       "25 2019-10-15 15:36:24+00:00         26 2019-10-15 16:07:06+00:00   \n",
       "26 2019-10-15 15:36:24+00:00         27 2019-10-15 16:07:17+00:00   \n",
       "27 2019-10-15 15:36:24+00:00         28 2019-10-15 16:08:12+00:00   \n",
       "28 2019-10-15 15:36:24+00:00         29 2019-10-15 16:09:06+00:00   \n",
       "29 2019-10-15 15:36:24+00:00         30 2019-10-15 16:10:00+00:00   \n",
       "30 2019-10-15 15:36:24+00:00         31 2019-10-15 16:10:56+00:00   \n",
       "31 2019-10-15 15:36:24+00:00         32 2019-10-15 16:11:43+00:00   \n",
       "32 2019-10-15 15:36:24+00:00         33 2019-10-15 16:12:54+00:00   \n",
       "33 2019-10-15 15:36:24+00:00         34 2019-10-15 16:14:05+00:00   \n",
       "34 2019-10-15 15:36:24+00:00         35 2019-10-15 16:16:27+00:00   \n",
       "35 2019-10-15 15:36:24+00:00         36 2019-10-15 16:17:12+00:00   \n",
       "36 2019-10-15 15:36:24+00:00         37 2019-10-15 16:18:05+00:00   \n",
       "37 2019-10-15 15:36:24+00:00         38 2019-10-15 16:20:55+00:00   \n",
       "38 2019-10-15 15:36:24+00:00         39 2019-10-15 16:21:26+00:00   \n",
       "39 2019-10-15 15:36:24+00:00         40 2019-10-15 16:21:48+00:00   \n",
       "40 2019-10-15 15:36:24+00:00         41 2019-10-15 16:25:39+00:00   \n",
       "41 2019-10-15 15:36:24+00:00         42 2019-10-15 16:27:33+00:00   \n",
       "42 2019-10-15 15:36:24+00:00         43 2019-10-15 16:31:37+00:00   \n",
       "43 2019-10-15 15:36:24+00:00         44 2019-10-15 16:32:08+00:00   \n",
       "44 2019-10-15 15:36:24+00:00         45 2019-10-15 16:32:22+00:00   \n",
       "45 2019-10-15 15:36:24+00:00         46 2019-10-15 16:34:42+00:00   \n",
       "46 2019-10-15 15:36:24+00:00         47 2019-10-15 16:36:14+00:00   \n",
       "47 2019-10-15 15:36:24+00:00         48 2019-10-15 16:37:26+00:00   \n",
       "48 2019-10-15 15:36:24+00:00         49 2019-10-15 16:38:06+00:00   \n",
       "49 2019-10-15 15:36:24+00:00         50 2019-10-15 16:39:01+00:00   \n",
       "50 2019-10-15 15:36:24+00:00         51 2019-10-15 16:43:30+00:00   \n",
       "\n",
       "          query_completed_on  elapsed_time_sec  \n",
       "0  2019-10-15 15:36:32+00:00                 4  \n",
       "1  2019-10-15 15:38:13+00:00                51  \n",
       "2  2019-10-15 15:39:06+00:00                50  \n",
       "3  2019-10-15 15:40:00+00:00                50  \n",
       "4  2019-10-15 15:39:32+00:00                 9  \n",
       "5  2019-10-15 15:42:20+00:00                87  \n",
       "6  2019-10-15 15:42:09+00:00                36  \n",
       "7  2019-10-15 15:42:03+00:00                13  \n",
       "8  2019-10-15 15:45:35+00:00               111  \n",
       "9  2019-10-15 15:51:19+00:00               226  \n",
       "10 2019-10-15 15:48:27+00:00                25  \n",
       "11 2019-10-15 15:50:21+00:00                68  \n",
       "12 2019-10-15 15:49:50+00:00                17  \n",
       "13 2019-10-15 15:49:46+00:00                 5  \n",
       "14 2019-10-15 15:50:57+00:00                36  \n",
       "15 2019-10-15 15:51:14+00:00                25  \n",
       "16 2019-10-15 15:52:48+00:00                51  \n",
       "17 2019-10-15 15:57:32+00:00               166  \n",
       "18 2019-10-15 15:55:41+00:00                26  \n",
       "19 2019-10-15 16:03:54+00:00               258  \n",
       "20 2019-10-15 16:00:29+00:00                25  \n",
       "21 2019-10-15 16:01:50+00:00                51  \n",
       "22 2019-10-15 16:03:18+00:00                68  \n",
       "23 2019-10-15 16:02:48+00:00                17  \n",
       "24 2019-10-15 16:09:12+00:00               197  \n",
       "25 2019-10-15 16:08:14+00:00                68  \n",
       "26 2019-10-15 16:07:25+00:00                 8  \n",
       "27 2019-10-15 16:09:03+00:00                51  \n",
       "28 2019-10-15 16:09:57+00:00                51  \n",
       "29 2019-10-15 16:10:51+00:00                51  \n",
       "30 2019-10-15 16:11:46+00:00                50  \n",
       "31 2019-10-15 16:12:20+00:00                37  \n",
       "32 2019-10-15 16:14:02+00:00                68  \n",
       "33 2019-10-15 16:15:13+00:00                68  \n",
       "34 2019-10-15 16:18:44+00:00               137  \n",
       "35 2019-10-15 16:17:51+00:00                39  \n",
       "36 2019-10-15 16:18:55+00:00                50  \n",
       "37 2019-10-15 16:23:41+00:00               166  \n",
       "38 2019-10-15 16:21:52+00:00                26  \n",
       "39 2019-10-15 16:22:06+00:00                18  \n",
       "40 2019-10-15 16:29:26+00:00               227  \n",
       "41 2019-10-15 16:29:24+00:00               111  \n",
       "42 2019-10-15 16:35:24+00:00               227  \n",
       "43 2019-10-15 16:32:33+00:00                25  \n",
       "44 2019-10-15 16:32:33+00:00                11  \n",
       "45 2019-10-15 16:36:59+00:00               137  \n",
       "46 2019-10-15 16:37:43+00:00                89  \n",
       "47 2019-10-15 16:38:34+00:00                68  \n",
       "48 2019-10-15 16:38:43+00:00                37  \n",
       "49 2019-10-15 16:39:53+00:00                52  \n",
       "50 2019-10-15 16:47:47+00:00               257  "
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT \n",
    "    TIMESTAMP_SECONDS(performance_test_num) AS test_date,\n",
    "    query_num,\n",
    "    TIMESTAMP_SECONDS(ran_on) AS query_ran_on,\n",
    "    TIMESTAMP_SECONDS(ran_on + elapsed_time_sec) AS query_completed_on,\n",
    "    elapsed_time_sec\n",
    "FROM `qwiklabs-resources.tpcds_2t_baseline.perf` # public table\n",
    "WHERE \n",
    " # Let's only pull the results from our most recent test\n",
    " performance_test_num = (SELECT MAX(performance_test_num) FROM `qwiklabs-resources.tpcds_2t_baseline.perf`)\n",
    "ORDER BY ran_on"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And the results of the complete test:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>test_date</th>\n",
       "      <th>count_queries_benchmarked</th>\n",
       "      <th>total_time_sec</th>\n",
       "      <th>total_time_min</th>\n",
       "      <th>fastest_query_time_sec</th>\n",
       "      <th>slowest_query_time_sec</th>\n",
       "      <th>avg_query_time_sec</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2019-10-15 15:36:24+00:00</td>\n",
       "      <td>51</td>\n",
       "      <td>3801</td>\n",
       "      <td>63.35</td>\n",
       "      <td>4</td>\n",
       "      <td>258</td>\n",
       "      <td>74.53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  test_date  count_queries_benchmarked  total_time_sec  \\\n",
       "0 2019-10-15 15:36:24+00:00                         51            3801   \n",
       "\n",
       "   total_time_min  fastest_query_time_sec  slowest_query_time_sec  \\\n",
       "0           63.35                       4                     258   \n",
       "\n",
       "   avg_query_time_sec  \n",
       "0               74.53  "
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT\n",
    "  TIMESTAMP_SECONDS(MAX(performance_test_num)) AS test_date,\n",
    "  COUNT(DISTINCT query_num) AS count_queries_benchmarked,\n",
    "  SUM(elapsed_time_sec) AS total_time_sec,\n",
    "  ROUND(SUM(elapsed_time_sec)/60,2) AS total_time_min,\n",
    "  MIN(elapsed_time_sec) AS fastest_query_time_sec,\n",
    "  MAX(elapsed_time_sec) AS slowest_query_time_sec,\n",
    "  ROUND(AVG(elapsed_time_sec),2) AS avg_query_time_sec\n",
    "FROM\n",
    "  `qwiklabs-resources.tpcds_2t_baseline.perf`\n",
    "WHERE\n",
    "  performance_test_num = (SELECT MAX(performance_test_num) FROM `qwiklabs-resources.tpcds_2t_baseline.perf`)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note the `total_time_sec` of __1766 seconds (or 29 minutes)__ which we will look to beat in future labs by applying BigQuery optimization techniques like:\n",
    "- Partitioning and Clustering\n",
    "- Nesting repeated fields\n",
    "- Denormalizing with STRUCT data types"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Congratulations!\n",
    "\n",
    "And there you have it! You successfully ran a performance benchmark test against your data warehouse. Continue on with the labs in this series to learn optimization strategies to boost your performance.\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
